# Define your item pipelines here
#
# Don't forget to add your pipeline to the ITEM_PIPELINES setting
# See: https://docs.scrapy.org/en/latest/topics/item-pipeline.html


# useful for handling different item types with a single interface
from itemadapter import ItemAdapter


class NewfkPipeline:
    def process_item(self, item, spider):
        return item


# import pymysql
#
#
# class MySQLPipeline:
#
#     def __init__(self):
#         database = "sunway_db"
#         # 连接database
#         self.__conn = pymysql.connect(host="192.168.2.205", user="jinhuitao", password="jinhuitao_123456",
#                                       database=database, charset="utf8", port=3306)
#         # 得到一个可以执行SQL语句的光标对象 # 执行完毕返回的结果集默认以元组显示
#         # self.__cursor = self.__conn.cursor()
#         # 得到一个可以执行SQL语句并且将结果作为字典返回的游标
#         self.__cursor = self.__conn.cursor(cursor=pymysql.cursors.DictCursor)
#         self.cache_count = 0
#         self.cache_list = []
#         self.cache_list1 = []
#         self.cache_list2 = []
#         self.cache_list3 = []
#         self.cache_list4 = []
#         self.cache_list5 = []
#         self.cache_list6 = []
#         self.cache_list7 = []
#         self.intt = 0
#         self.intt1 = 0
#
#     def process_item(self, item, spider):
#
#         try:
#
#             self.cache_list.append(
#                 (item["guolv"], item["id1"], item["sku1"], item["asin1"], item["asinId1"], item["isFba1"],
#                  item["title1"], item["newtime"], item["belongDate"], item["price"], item["bsrRank"],
#                  item["bsrCategory"], item["bsrRankLs"], item["bsrCategoryLs"], item["adTraffic"], item["review"],
#                  item["sessions"], item["traffic"], item["naturalTraffic"], item["ucr"], item["ocr"],
#                  item["naturalCR"], item["orders"], item["adOrders"], item["quantities"], item["goodsSale"],
#                  item["totalIncome"], item["costs"], item["profits"], item["profitsRate"], item["fbaFee"],
#                  item["salesCommission"], item["promotionFee"], item["unitCost"], item["fbaFeeRate"],
#                  item["fbmShippingCostRate"], item["refundManageFee"], item["returnOrders"], item["returnRate"],
#                  item["impressions"], item["clicks"], item["ctr"], item["adCR"], item["adSales"], item["spend"],
#                  item["aCoS"], item["Roas"], item["acoAs"], item["asoAs"], item["reviewRate"], item["site1"],
#                  item["accountName"]))
#
#             res = self.__cursor.executemany(
#                 f"REPLACE into father_asin(`guolv`,`id1`,`sku1`,`asin1`,`asinId1`,`isFba1`,`title1`,`newtime`,`belongDate`,`price`,`bsrRank`,`bsrCategory`,`bsrRankLs`,`bsrCategoryLs`,`adTraffic`,`review`,`sessions`,`traffic`,`naturalTraffic`,`ucr`,`ocr`,`naturalCR`,`orders`,`adOrders`,`quantities`,`goodsSale`,`totalIncome`,`costs`,`profits`,`profitsRate`,`fbaFee`,`salesCommission`,`promotionFee`,`unitCost`,`fbaFeeRate`,`fbmShippingCostRate`,`refundManageFee`,`returnOrders`,`returnRate`,`impressions`,`clicks`,`ctr`,`adCR`,`adSales`,`spend`,`aCoS`,`Roas`,`acoAs`,`asoAs`,`reviewRate`,`site1`,`accountName`) values(%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s)",
#                 self.cache_list)
#             self.__conn.commit()
#             self.cache_list.clear()
#             return item
#
#
#         except Exception as g:
#             try:
#                 self.cache_list1.append(
#                     (item["newtime"], item["asin1"], item["url_fa"]))
#
#                 res = self.__cursor.executemany(
#                     f"REPLACE into lin_father_asin(`newtime`,`asin1`,`url_fa`) values(%s,%s,%s)",
#                     self.cache_list1)
#                 self.__conn.commit()
#                 self.cache_list1.clear()
#                 return item
#
#
#
#
#
#
#
#             except Exception as g:
#                 try:
#
#                     self.cache_list2.append(
#                         (item["fa_son"], item["id2"], item["sku2"], item["asin2"], item["asinId2"], item["isFba2"],
#                          item["title2"],
#                          item["newtime"], item["belongDate2"], item["price2"], item["bsrRank2"],
#                          item["bsrCategory2"],
#                          item["bsrRankLs2"], item["bsrCategoryLs2"], item["adTraffic2"], item["review2"],
#                          item["sessions2"],
#                          item["traffic2"], item["naturalTraffic2"], item["ucr2"], item["ocr2"], item["naturalCR2"],
#                          item["orders2"], item["adOrders2"], item["quantities2"], item["goodsSale2"],
#                          item["totalIncome2"],
#                          item["costs2"], item["profits2"], item["profitsRate2"], item["fbaFee2"],
#                          item["salesCommission2"],
#                          item["promotionFee2"], item["unitCost2"], item["fbaFeeRate2"],
#                          item["fbmShippingCostRate2"], item["refundManageFee2"], item["returnOrders2"],
#                          item["returnRate2"],
#                          item["impressions2"], item["clicks2"], item["ctr2"], item["adCR2"], item["adSales2"],
#                          item["spend2"],
#                          item["aCoS2"], item["Roas2"], item["acoAs2"], item["asoAs2"], item["reviewRate2"],
#                          item["site2"],
#                          item["accountName2"], item["father_asin2"], item["son_zhu"]))
#
#                     res = self.__cursor.executemany(
#                         f"REPLACE into son_asin(`fa_son`,`id2`,`sku2`,`asin2`,`asinId2`,`isFba2`,`title2`,`newtime`,`belongDate2`,`price2`,`bsrRank2`,`bsrCategory2`,`bsrRankLs2`,`bsrCategoryLs2`,`adTraffic2`,`review2`,`sessions2`,`traffic2`,`naturalTraffic2`,`ucr2`,`ocr2`,`naturalCR2`,`orders2`,`adOrders2`,`quantities2`,`goodsSale2`,`totalIncome2`,`costs2`,`profits2`,`profitsRate2`,`fbaFee2`,`salesCommission2`,`promotionFee2`,`unitCost2`,`fbaFeeRate2`,`fbmShippingCostRate2`,`refundManageFee2`,`returnOrders2`,`returnRate2`,`impressions2`,`clicks2`,`ctr2`,`adCR2`,`adSales2`,`spend2`,`aCoS2`,`Roas2`,`acoAs2`,`asoAs2`,`reviewRate2`,`site2`,`accountName2`,`father_asin2`,`son_zhu`) values(%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s)",
#                         self.cache_list2)
#                     self.__conn.commit()
#                     self.cache_list2.clear()
#                     return item
#
#
#
#
#
#
#
#
#                 except Exception as g:
#                     try:
#                         self.cache_list3.append(
#                             (item["newtime"], item["asin2"], item["url_son"]))
#
#                         res = self.__cursor.executemany(
#                             f"REPLACE into lin_son_asin(`newtime`,`asin2`,`url_son`) values(%s,%s,%s)",
#                             self.cache_list3)
#                         self.__conn.commit()
#                         self.cache_list3.clear()
#                         return item
#                     except Exception as g:
#                         try:
#
#                             self.cache_list4.append(
#                                 (item["fa_guolv"], item["yesterday1"], item["newtime"], item["asin1"], item["sku1"],
#                                  item["accountName1"], item["site1"], item["available"], item["reserved"],
#                                  item["customerOrdersReservedQty"], item["reservedTransfersQty"],
#                                  item["reservedProcessingQty"], item["unvailable"], item["imagePath"],
#                                  item["productGroup"], item["reviewScore"], item["reviewCount"],
#                                  item["workInbound"], item["shipInbound"],
#                                  item["inbound"]))
#
#                             res = self.__cursor.executemany(
#                                 f"REPLACE into stock_fa(`fa_guolv`,`belongDate`,`newtime`,`asin1`,`sku1`,`accountName1`,`site1`,`available`,`reserved`,`customerOrdersReservedQty`,`reservedTransfersQty`,`reservedProcessingQty`,`unvailable`,`imagePath`,`productGroup`,`reviewScore`,`reviewCount`,`workInbound`,`shipInbound`,`inbound`) values(%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s)",
#                                 self.cache_list4)
#                             self.__conn.commit()
#                             self.cache_list4.clear()
#                             return item
#
#
#
#
#                         except Exception as g:
#
#                             self.cache_list5.append(
#                                 (item["son_guolv"], item["father_asin"], item["yesterday1"],
#                                  item["newtime"], item["asin2"], item["sku2"], item["accountName2"],
#                                  item["site2"], item["available2"], item["reserved2"],
#                                  item["customerOrdersReservedQty2"], item["reservedTransfersQty2"],
#                                  item["reservedProcessingQty2"], item["unvailable2"], item["imagePath2"],
#                                  item["productGroup2"], item["reviewScore2"], item["reviewCount2"],
#                                  item["workInbound2"], item["shipInbound2"],
#                                  item["inbound2"]))
#
#                             res = self.__cursor.executemany(
#                                 f"REPLACE into stock_son(`son_guolv`,`father_asin2`,`belongDate2`,`newtime`,`asin2`,`sku2`,`accountName2`,`site2`,`available2`,`reserved2`,`customerOrdersReservedQty2`,`reservedTransfersQty2`,`reservedProcessingQty2`,`unvailable2`,`imagePath2`,`productGroup2`,`reviewScore2`,`reviewCount2`,`workInbound2`,`shipInbound2`,`inbound2`) values(%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s)",
#                                 self.cache_list5)
#                             self.__conn.commit()
#                             self.cache_list5.clear()
#                             return item
#
#     def close_spider(self, spider):
#         # 关闭光标对象
#         self.__cursor.close()
#         # 关闭数据库连接
#         self.__conn.close()


import pymysql
from twisted.enterprise import adbapi


# 异步更新操作
class MySQLPipeline(object):
    def __init__(self, dbpool):
        self.dbpool = dbpool

        self.cache_count = 0
        self.cache_list = []
        self.cache_list1 = []
        self.cache_list2 = []
        self.cache_list3 = []
        self.cache_list4 = []
        self.cache_list5 = []
        self.cache_list6 = []
        self.cache_list7 = []
        self.intt = 0
        self.intt1 = 0

    @classmethod
    def from_settings(cls, settings):  # 函数名固定，会被scrapy调用，直接可用settings的值
        """
    数据库建立连接
    :param settings: 配置参数
    :return: 实例化参数
    """
        adbparams = dict(
            host='14.29.212.205',#10.10.0.207   14.29.212.205
            db='sunway_db',
            user='asinuser',
            password='asinuser_SW168',
            cursorclass=pymysql.cursors.DictCursor,  # 指定cursor类型
            port=8306
        )


        # 连接数据池ConnectionPool，使用pymysql或者Mysqldb连接
        dbpool = adbapi.ConnectionPool('pymysql', **adbparams)
        # 返回实例化参数
        return cls(dbpool)

    def process_item(self, item, spider):
        """
    使用twisted将MySQL插入变成异步执行。通过连接池执行具体的sql操作，返回一个对象
    """
        # print('0000')
        query = self.dbpool.runInteraction(self.do_insert, item)  # 指定操作方法和操作数据
        # 添加异常处理
        query.addCallback(self.handle_error)  # 处理异常

    def do_insert(self, cursor, item):
        # 对数据库进行插入操作，并不需要commit，twisted会自动commit
        # print('1111')
        try:

            insert_sql = """REPLACE into father_asin(`guolv`,`id1`,`sku1`,`asin1`,`asinId1`,`isFba1`,`title1`,`newtime`,`belongDate`,`price`,`bsrRank`,`bsrCategory`,`bsrRankLs`,`bsrCategoryLs`,`adTraffic`,`review`,`sessions`,`traffic`,`naturalTraffic`,`ucr`,`ocr`,`naturalCR`,`orders`,`adOrders`,`quantities`,`goodsSale`,`totalIncome`,`costs`,`profits`,`profitsRate`,`fbaFee`,`salesCommission`,`promotionFee`,`unitCost`,`fbaFeeRate`,`fbmShippingCostRate`,`refundManageFee`,`returnOrders`,`returnRate`,`impressions`,`clicks`,`ctr`,`adCR`,`adSales`,`spend`,`aCoS`,`Roas`,`acoAs`,`asoAs`,`reviewRate`,`site1`,`accountName`) values(%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s)"""
            cursor.execute(insert_sql, (
                item["guolv"], item["id1"], item["sku1"], item["asin1"], item["asinId1"], item["isFba1"],
                item["title1"], item["newtime"], item["belongDate"], item["price"], item["bsrRank"],
                item["bsrCategory"], item["bsrRankLs"], item["bsrCategoryLs"], item["adTraffic"], item["review"],
                item["sessions"], item["traffic"], item["naturalTraffic"], item["ucr"], item["ocr"],
                item["naturalCR"], item["orders"], item["adOrders"], item["quantities"], item["goodsSale"],
                item["totalIncome"], item["costs"], item["profits"], item["profitsRate"], item["fbaFee"],
                item["salesCommission"], item["promotionFee"], item["unitCost"], item["fbaFeeRate"],
                item["fbmShippingCostRate"], item["refundManageFee"], item["returnOrders"], item["returnRate"],
                item["impressions"], item["clicks"], item["ctr"], item["adCR"], item["adSales"], item["spend"],
                item["aCoS"], item["Roas"], item["acoAs"], item["asoAs"], item["reviewRate"], item["site1"],
                item["accountName"]))

        except Exception as g:
            try:
                insert = """REPLACE into lin_father_asin(`newtime`,`asin1`,`url_fa`) values(%s,%s,%s)"""
                cursor.execute(insert, (item["newtime"], item["asin1"], item["url_fa"]))
            except Exception as g:
                try:

                    insert2 = """REPLACE into son_asin(`fa_son`,`id2`,`sku2`,`asin2`,`asinId2`,`isFba2`,`title2`,`newtime`,`belongDate2`,`price2`,`bsrRank2`,`bsrCategory2`,`bsrRankLs2`,`bsrCategoryLs2`,`adTraffic2`,`review2`,`sessions2`,`traffic2`,`naturalTraffic2`,`ucr2`,`ocr2`,`naturalCR2`,`orders2`,`adOrders2`,`quantities2`,`goodsSale2`,`totalIncome2`,`costs2`,`profits2`,`profitsRate2`,`fbaFee2`,`salesCommission2`,`promotionFee2`,`unitCost2`,`fbaFeeRate2`,`fbmShippingCostRate2`,`refundManageFee2`,`returnOrders2`,`returnRate2`,`impressions2`,`clicks2`,`ctr2`,`adCR2`,`adSales2`,`spend2`,`aCoS2`,`Roas2`,`acoAs2`,`asoAs2`,`reviewRate2`,`site2`,`accountName2`,`father_asin2`,`son_zhu`) values(%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s)"""
                    cursor.execute(insert2, (
                        item["fa_son"], item["id2"], item["sku2"], item["asin2"], item["asinId2"], item["isFba2"],
                        item["title2"],
                        item["newtime"], item["belongDate2"], item["price2"], item["bsrRank2"],
                        item["bsrCategory2"],
                        item["bsrRankLs2"], item["bsrCategoryLs2"], item["adTraffic2"], item["review2"],
                        item["sessions2"],
                        item["traffic2"], item["naturalTraffic2"], item["ucr2"], item["ocr2"], item["naturalCR2"],
                        item["orders2"], item["adOrders2"], item["quantities2"], item["goodsSale2"],
                        item["totalIncome2"],
                        item["costs2"], item["profits2"], item["profitsRate2"], item["fbaFee2"],
                        item["salesCommission2"],
                        item["promotionFee2"], item["unitCost2"], item["fbaFeeRate2"],
                        item["fbmShippingCostRate2"], item["refundManageFee2"], item["returnOrders2"],
                        item["returnRate2"],
                        item["impressions2"], item["clicks2"], item["ctr2"], item["adCR2"], item["adSales2"],
                        item["spend2"],
                        item["aCoS2"], item["Roas2"], item["acoAs2"], item["asoAs2"], item["reviewRate2"],
                        item["site2"],
                        item["accountName2"], item["father_asin2"], item["son_zhu"]))

                except Exception as g:
                    try:
                        insert3 = """REPLACE into lin_son_asin(`newtime`,`asin2`,`url_son`) values(%s,%s,%s)"""
                        cursor.execute(insert3, (item["newtime"], item["asin2"], item["url_son"]))
                    except Exception as g:
                        try:

                            insert4 = """REPLACE into stock_fa(`fa_guolv`,`belongDate`,`newtime`,`asin1`,`sku1`,`accountName1`,`site1`,`available`,`reserved`,`customerOrdersReservedQty`,`reservedTransfersQty`,`reservedProcessingQty`,`unvailable`,`imagePath`,`productGroup`,`reviewScore`,`reviewCount`,`workInbound`,`shipInbound`,`inbound`) values(%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s)"""
                            cursor.execute(insert4, (
                                item["fa_guolv"], item["yesterday1"], item["newtime"], item["asin1"], item["sku1"],
                                item["accountName1"], item["site1"], item["available"], item["reserved"],
                                item["customerOrdersReservedQty"], item["reservedTransfersQty"],
                                item["reservedProcessingQty"], item["unvailable"], item["imagePath"],
                                item["productGroup"], item["reviewScore"], item["reviewCount"],
                                item["workInbound"], item["shipInbound"],
                                item["inbound"]))

                        except Exception as g:

                            insert5 = """REPLACE into stock_son(`son_guolv`,`father_asin2`,`belongDate2`,`newtime`,`asin2`,`sku2`,`accountName2`,`site2`,`available2`,`reserved2`,`customerOrdersReservedQty2`,`reservedTransfersQty2`,`reservedProcessingQty2`,`unvailable2`,`imagePath2`,`productGroup2`,`reviewScore2`,`reviewCount2`,`workInbound2`,`shipInbound2`,`inbound2`) values(%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s)"""
                            cursor.execute(insert5, (item["son_guolv"], item["father_asin"], item["yesterday1"],
                                                     item["newtime"], item["asin2"], item["sku2"],
                                                     item["accountName2"],
                                                     item["site2"], item["available2"], item["reserved2"],
                                                     item["customerOrdersReservedQty2"],
                                                     item["reservedTransfersQty2"],
                                                     item["reservedProcessingQty2"], item["unvailable2"],
                                                     item["imagePath2"],
                                                     item["productGroup2"], item["reviewScore2"],
                                                     item["reviewCount2"],
                                                     item["workInbound2"], item["shipInbound2"],
                                                     item["inbound2"]))

    def handle_error(self, failure):
        if failure:
            # 打印错误信息
            print(failure)
